package edu.yuhf.dao.mysql;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;

import org.apache.log4j.Logger;

import com.yuhf.dbutils.JdbcTemplate;
import com.yuhf.dbutils.ResultSetHandler;

import edu.yuhf.dao.convert.ListMapHandler;
import edu.yuhf.dao.convert.UserHandler;
import edu.yuhf.dao.iface.UserDao;
import edu.yuhf.domain.Page;
import edu.yuhf.domain.User;

public class UserJdbcDaoImpl implements UserDao {
	
	private static Logger log=Logger.getLogger(UserJdbcDaoImpl.class);
	private static final String COLNUM_NAME=" id,user_name as userName,nick_name as nickName,password,sex,email,phone ";

	@Override
	public List<User> showAllUser() {
		String sql="select "+COLNUM_NAME+" from uim_user";
		UserHandler userHandler=new UserHandler();
		List<User> list=JdbcTemplate.query(sql,userHandler);
		return list;
	}

	@Override
	public int queryUserForNameAndPwd(String userName, String password) {
		String sql="select count(*) from uim_user where user_name=? and password=?";
		int count=0;
		count=JdbcTemplate.queryForCount(sql, userName,password);
		return count;
	}

	@Override
	public Optional<User> queryUser(String userName, String password) {
		User user=null;
		String sql="select "+COLNUM_NAME+" from uim_user where user_name=? and password=?";
		List<User> list=JdbcTemplate.query(sql,new UserHandler(),userName,password);
		Optional optional=Optional.ofNullable(null!=list&&list.size()!=0?list.get(0):null);
		return optional;
	}

	@Override
	public int insertForUserId(User user) {
		String sql="insert into uim_user(user_name,nick_name,password,sex,email,phone) values(?,?,?,?,?,?)";
		int id=0;
		id=JdbcTemplate.updateForKey(sql, user.getUserName(),user.getNickName(),user.getPassword(),user.getSex(),user.getEmail(),user.getPhone());
		return id;
	}

	@Override
	public int checkUserName(String userName) {
		int rowNumber=0;
		String sql="select count(*) from uim_user where user_name=?";
		rowNumber=JdbcTemplate.queryForCount(sql, userName);
		return rowNumber;
	}

	@Override
	public List<Map<String, Object>> queryAllUserAndUserDetail() {
		String sql="select u.id,u.user_name as userName,u.nick_name as nickName,u.password,u.sex,u.email,u.phone,np.name as npName,np.name,ud.hobby_code as hobbyCode,ud.birthday,ud.np_code as npCode "
				+ "from uim_user u left outer join uim_user_detail ud on u.id=ud.user_id left outer join uim_np np on ud.np_code=np.code";
		List<Map<String,Object>> list=JdbcTemplate.query(sql,(rs)->{
			List<Map<String,Object>> list0=new ArrayList();
			try {
				while(rs.next()) {
					Map<String,Object> map=new HashMap<String,Object>();
					for(int i=0,len=rs.getMetaData().getColumnCount();i<len;i++) {
						map.put(rs.getMetaData().getColumnName(i+1), rs.getObject(i+1));
					}
					list0.add(map);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return list0;
		});		
		return list;
	}

	@Override
	public int deleteUser(String ids) {
		int rowNumber=0;
		String sql="delete from uim_user where id in ("+ids+")";
		rowNumber=JdbcTemplate.update(sql);
		return rowNumber;
	}

	@Override
	public Map<String, Object> queryUserAndUserDetailById(int id) {
		Map<String,Object> userAndUserDetail=null;
		String sql="select u.id,u.user_name as userName,u.nick_name as nickName,u.password,u.sex,u.email,u.phone,np.name,ud.hobby_code as hobbyCode,ud.birthday,ud.np_code as npCode "
				+ "from uim_user u left outer join uim_user_detail ud on u.id=ud.user_id left outer join uim_np np on ud.np_code=np.code "
				+ "where u.id=?";
		userAndUserDetail=JdbcTemplate.query(sql, rs->{
			Map<String,Object> uaud=null;
			try {
				if(rs.next()) {
					uaud=new HashMap<>();
					for(int i=0,len=rs.getMetaData().getColumnCount();i<len;i++) {
						uaud.put(rs.getMetaData().getColumnName(i+1), rs.getObject(i+1));
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return uaud;
		}, id);
		return userAndUserDetail;
	}

	@Override
	public int updateUser(User user) {
		int rowNumber=0;
		String sql="update uim_user set user_name=?,nick_name=?,password=?,sex=?,email=?,phone=? where id=?";
		rowNumber=JdbcTemplate.update(sql, user.getUserName(),user.getNickName(),user.getPassword(),user.getSex(),user.getEmail(),user.getPhone(),user.getId());
		return rowNumber;
	}

	@Override
	public void queryAllUserAndUserDetailByPage(Page<List<Map<String,Object>>> page) {
		StringBuilder sb=new StringBuilder("select ");
		sb.append("u.id,u.user_name as userName,u.nick_name as nickName,u.password,u.sex,u.email,u.phone,np.name as npName,ud.hobby_code as hobbyCode,date_format(ud.birthday,'%Y年%m月%d日') as birthday,ud.np_code as npCode ");
		sb.append("from uim_user u left outer join uim_user_detail ud on u.id=ud.user_id ");
		sb.append("left outer join uim_np np on ud.np_code=np.code where 1=1 ");
		if(!"".equals(page.getKeyword())) {
			sb.append("and u.user_name like ?");
		}
		sb.append(" limit ?,?");
		int beginPage=(page.getCurrentPage()-1)*page.getPageNumber();
		int rowLength=page.getPageNumber();
		String keyword=page.getKeyword();
		ResultSetHandler<List<Map<String,Object>>> rsh=new ListMapHandler();
		List<Map<String,Object>> list=null;
		if("".equals(keyword)) {
			list=JdbcTemplate.query(sb.toString(),rsh,beginPage,rowLength);		
		}else {
			list=JdbcTemplate.query(sb.toString(),rsh,"%"+keyword+"%",beginPage,rowLength);					
		}
		page.setList(list);
	}

	@Override
	public int queryTotalRowNumber() {
		int totalRowNumber=0;
		String sql="select count(*) from uim_user u left outer join uim_user_detail ud on u.id=ud.user_id left outer join uim_np np on ud.np_code=np.code";
		totalRowNumber=JdbcTemplate.queryForCount(sql);
		return totalRowNumber;
	}
	@Override
	public int queryTotalRowNumber(String keyword) {
		int totalRowNumber=0;
		String sql="select count(*) from uim_user u left outer join uim_user_detail ud on u.id=ud.user_id left outer join uim_np np on ud.np_code=np.code "
				+ "where u.user_name like ?";
		totalRowNumber=JdbcTemplate.queryForCount(sql,"%"+keyword+"%");
		return totalRowNumber;
	}

	@Override
	public Optional<User> queryUserById(int id) {
		List<User> list=null;
		Optional<User> user=null;
		String sql="select "+COLNUM_NAME+" from uim_user where id=?";
		list=JdbcTemplate.query(sql, new UserHandler(), id);
		user=Optional.ofNullable(null!=list&&list.size()!=0?list.get(0):null);
		return user;
	}
}
